package scct.ZhuZhouXing.dao;

import scct.ZhuZhouXing.model.Members;
import scct.ZhuZhouXing.model.User;
import scct.ZhuZhouXing.ui.MainFrom;
import scct.ZhuZhouXing.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MembersDao {
    public List<Members> gettallMembers(){
        List<Members> list = new ArrayList<>();
//        获取数据库连接对象
        Connection conn = DBUtils.getConnection();
//        查询数据库
        String sql = "select * from sp_members";
        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String sex = rs.getString("sex");
                String account = rs.getString("account");
                String password = rs.getString("password");
                String member = rs.getString("member");
                String number = rs.getString("number");
                list.add(new Members(id,username,sex,account,password,member,number));
            }
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    //添加的方法
    public boolean addMembers(String username,String sex,String account,String password,String member,String number) {
//        添加是否成果的标志
        int result = 0;
        Connection conn = DBUtils.getConnection();
        String insert_sql = "insert into sp_members values(null,?,?,?,?,?,?);";
        try {
            PreparedStatement pstmt = conn.prepareStatement(insert_sql);
            pstmt.setString(1, username);
            pstmt.setString(2, sex);
            pstmt.setString(3, account);
            pstmt.setString(4, password);
            pstmt.setString(5, member);
            pstmt.setString(6, number);
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result == 1;
    }

//    判断名字是否重复
    public boolean isDupname(String username) {
        int count = 0;
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_members where username=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                count = 1;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count > 0;
    }

//    修改

    public boolean updateMembers(int id,String username,String sex,String account,String password,String member,String number){
        int count=0;
        Connection conn =DBUtils.getConnection();
        String update_sql="update sp_members set username=?,sex=?,account=?,password=?,member=?,number=? where id=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(update_sql);
            pstmt.setString(1, username);
            pstmt.setString(2, sex);
            pstmt.setString(3, account);
            pstmt.setString(4, password);
            pstmt.setString(5, member);
            pstmt.setString(6, number);
            pstmt.setInt(7,id);
            count=pstmt.executeUpdate();
        }catch (SQLException e){
            throw new RuntimeException(e);
    }
        return count>0;
    }
//    查询单个条件
        public List<Members>getSelect(String name){
        List<Members> list= new ArrayList<>();
        Connection conn =DBUtils.getConnection();
        String sql="select * from sp_members where username like '%"+name+"%'";
        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            ResultSet rs =pstmt.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String username = rs.getString(2);
                String sex = rs.getString("sex");
                String account = rs.getString("account");
                String password = rs.getString("password");
                String member = rs.getString("member");
                String number = rs.getString("number");
                list.add(new Members(id,username,sex,account,password,member,number));
            }
            DBUtils.closeConnection(conn);
        }catch (SQLException e){
            throw new RuntimeException(e);
        }
        return list;
        }
    public Members getMembersByid(int id){
        Members members=null;
        Connection conn = DBUtils.getConnection();
        String sql = "select * from sp_members where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next())
            {
                String username = rs.getString("username");
                String sex = rs.getString("sex");
                String account = rs.getString("account");
                String password = rs.getString("password");
                String member = rs.getString("member");
                String number = rs.getString("number");
                members = new Members(id,username,sex,account,password,member,number);
            }
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return members;
    }
    public boolean deMembers(int id){
        int num=0;
        Connection conn=DBUtils.getConnection();
        String de_sql="delete from sp_members where id=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(de_sql);
            pstmt.setInt(1,id);
            num=pstmt.executeUpdate();
            DBUtils.closeConnection(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return num>0;
    }
}
